using System;
using System.Data;
using Microsoft.SharePoint;

namespace PS.WebParts.Rollup.Query
{
    public class StorageDateOffset
    {
        public TimeSpan GetOffset()
        {
            TimeSpan res;
            using(SPSite site = new SPSite("http://wss-server"))
            {
                using(SPWeb web = site.OpenWeb("rollup"))
                {
                    SPList list = GetList(web);
                    if(!list.Fields.ContainsField("datetime"))
                    {
                        list.Fields.Add("datetime", SPFieldType.DateTime, true);
                    }

                    for(int i=0; i<list.Items.Count; ++i)
                    {
                        list.Items.Delete(i);
                    }
                    
                    SPListItem listItem = list.Items.Add();
                    DateTime today = DateTime.Now;
                    listItem["datetime"] = today;
                    listItem.Update();
                    DateTime dbToday = GetDBValue(list.ID, web);
                    res = (today - dbToday);
                }
            }
            return res;
        }

        private SPList GetList(SPWeb web)
        {
            try
            {
                SPList list = web.Lists["datetime_list"];
                return list;
            }
            catch (Exception)
            {
                Guid listId = web.Lists.Add("datetime_list", "datetime_list", SPListTemplateType.CustomGrid);
                SPList list = web.Lists[listId];
                return list;
            }
        }

        private DateTime GetDBValue(Guid listID, SPWeb web)
        {
            using(PSSiteDataQuery query = new PSSiteDataQuery(CacheSingleton.Instance))
            {
                query.Lists.Ids.Add(listID);
                query.Fields.Add("datetime");
                DataTable res = query.GetRollupData(web, false);
                if(res.Rows.Count > 0 && res.Columns.Count == 1)
                {
                    return (DateTime) res.Rows[0][0];
                }
                throw new DataException("datetime offset: no appropriate list items in database");
            }
        }
    }
}
